﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using SqlSugar;

namespace WinFrm
{
    public partial class Form1 : Form
    {
        ConnectionConfig config = new ConnectionConfig();
        SqlSugarClient client = null;

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            config.DbType = SqlSugar.DbType.SqlServer;
            config.ConnectionString = "server=.;uid=sa;pwd=as;database=Sugar";
            config.IsAutoCloseConnection = true;

            client = new SqlSugarClient(config);

            //client.Aop.OnLogExecuted = (sql, pars) =>
            //{
            //    MessageBox.Show($"当前操作执行的SQL为{sql}");
            //};
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //首次使用初始化数据库和表结构
            client.DbMaintenance.CreateDatabase();
            client.CodeFirst.SetStringDefaultLength(20).InitTables(typeof(Models.Student));
            client.CodeFirst.SetStringDefaultLength(20).InitTables(typeof(Models.RollCall));
            MessageBox.Show("数据库初始化成功");
        }

        private void button2_Click(object sender, EventArgs e)
        {
            Models.Student s = new Models.Student();
            s.StuNo = "001";
            s.Name = "张三";
            s.Gender = "男";

            int result = client.Insertable<Models.Student>(s).ExecuteCommand();
            MessageBox.Show($"成功添加了{result}条");
        }

        private void button3_Click(object sender, EventArgs e)
        {
            Models.Student s = new Models.Student();
            s.StuNo = "001";
            s.Name = "张三四";
            s.Gender = "女";

            int result = client.Updateable<Models.Student>(s).ExecuteCommand();
            MessageBox.Show($"成功修改了{result}条");
        }

        private void button4_Click(object sender, EventArgs e)
        {
            Models.RollCall r = new Models.RollCall();
            //r.Id = 1; //数据库自动维护的，代码不用指定
            r.StuNo = "001";
            r.Status = false;

            //int result = client.Insertable<Models.RollCall>(r).ExecuteCommand();
            //MessageBox.Show($"成功添加了{result}条");
            int id = client.Insertable<Models.RollCall>(r).ExecuteReturnIdentity();
            MessageBox.Show($"新添加数据的id为{id}");
        }

        private void button5_Click(object sender, EventArgs e)
        {
            Models.RollCall r = new Models.RollCall();
            r.Id = 3;

            int result = client.Deleteable<Models.RollCall>(r).ExecuteCommand();
            MessageBox.Show($"成功删除了{result}条");
        }

        private void button6_Click(object sender, EventArgs e)
        {
            List<Models.Student> list = client.Queryable<Models.Student>().ToList();
            dataGridView1.DataSource = list;
        }

        private void button7_Click(object sender, EventArgs e)
        {
            List<Models.Student> list = new List<Models.Student>();
            list.Add(new Models.Student { StuNo = "002", Name = "李三", Gender = "男" });
            list.Add(new Models.Student { StuNo = "003", Name = "王四", Gender = "女" });
            list.Add(new Models.Student { StuNo = "004", Name = "孙1", Gender = "女" });

            int result = client.Insertable(list).ExecuteCommand();
            MessageBox.Show($"成功添加了{result}条");
        }

        private void button8_Click(object sender, EventArgs e)
        {
            Dictionary<String, Object> dic = new Dictionary<string, object>();
            dic["StuNo"] = "004";
            dic["Name"] = "关羽";
            dic["Gender"] = "男";

            int result = client.Insertable<Models.Student>(dic).ExecuteCommand();
            MessageBox.Show($"成功添加了{result}条");
        }

        private void button9_Click(object sender, EventArgs e)
        {
            Models.Student s = new Models.Student();
            s.StuNo = "001";
            s.Password = "12345678";

            //int result = client.Updateable<Models.Student>(s).UpdateColumns(new String[] { "Password" }).ExecuteCommand();
            int result = client.Updateable<Models.Student>(s).UpdateColumns(s=>s.Password).ExecuteCommand();
            MessageBox.Show($"成功修改了{result}条");
        }

        private void button10_Click(object sender, EventArgs e)
        {
            //所有性别为男的同学，密码初始化为654321
            int result = client.Updateable<Models.Student>().SetColumns("Password", "654321").
                Where(s => s.Gender == "男").ExecuteCommand();
            MessageBox.Show($"成功修改了{result}条");
        }

        private void button11_Click(object sender, EventArgs e)
        {
            List<String> pks = new List<string>();
            pks.Add("001");
            pks.Add("003");

            int result = client.Deleteable<Models.Student>().In(pks).ExecuteCommand();//默认是按主键匹配
            MessageBox.Show($"成功删除了{result}条");
        }

        private void button12_Click(object sender, EventArgs e)
        {
            int result = client.Deleteable<Models.Student>().Where(s => s.Gender == "男").ExecuteCommand();//默认是按主键匹配
            MessageBox.Show($"成功删除了{result}条");
        }

        private void button13_Click(object sender, EventArgs e)
        {
            List<Models.Student> list = new List<Models.Student>();
            list.Add(new Models.Student { StuNo = "002", Name = "李三", Gender = "男" });
            list.Add(new Models.Student { StuNo = "003", Name = "王石", Gender = "男" });
            list.Add(new Models.Student { StuNo = "005", Name = "孙er", Gender = "女" });

            //int result = client.Storageable(list).ExecuteCommand();
            //MessageBox.Show($"成功保存了{result}条");
            var x = client.Storageable(list).ToStorage(); //数据划分/分组
            int result_add = x.AsInsertable.ExecuteCommand();//对要添加的数据执行insert操作
            int result_update = x.AsUpdateable.IgnoreColumns(stu=>stu.Gender).ExecuteCommand();//对要修改的数据执行update操作
            MessageBox.Show($"添加了{result_add}条；修改了{result_update}条");
        }

        private void button14_Click(object sender, EventArgs e)
        {
            int result_w = client.Updateable<Models.Student>().SetColumns("Balance", 200)
                .Where(stu => stu.Gender == "女").ExecuteCommand();
            int result_m = client.Updateable<Models.Student>().SetColumns("Balance", 100)
                .Where(stu => stu.Gender == "男").ExecuteCommand();
            MessageBox.Show($"初始化女生余额{result_w}人");
            MessageBox.Show($"初始化男生余额{result_m}人");
        }

        private void button15_Click(object sender, EventArgs e)
        {
            String from = stu_From.Text; //转出账号
            String to = stu_To.Text; //转入账号
            float m = float.Parse(money.Text); //钱数

            //事务处理
            try
            {
                client.Ado.BeginTran();//开启事务

                //1.转出账号 减操作
                client.Updateable<Models.Student>().SetColumns(stu => stu.Balance == (stu.Balance - m)) //==表示赋值
                    .Where(stu => stu.StuNo == from).ExecuteCommand();

                //2.转入账号 加操作
                client.Updateable<Models.Student>().SetColumns(stu => stu.Balance == (stu.Balance + m))
                    .Where(stu => stu.StuNo == to).ExecuteCommand();

                client.Ado.CommitTran();//提交事务
                MessageBox.Show("转账成功！");
            }
            catch
            {
                client.Ado.RollbackTran();//回滚事务，还原到数据的原始状态
                MessageBox.Show("转账失败！");
            }
        }
    }
}
